#include "bizqueryhelper.h"
#include "dbhelper.h"
#include "iostream"
#include "dateinteger.h"
#include "toolhelper.h"

bizQueryHelper::bizQueryHelper()
{

}

QList<QVariantMap> bizQueryHelper::GetIncomeRptGroupByType(dbhelper db,QString filter)
{
    QStringList sqlList;
    sqlList.append(" select t1.fitypeid,t1.ftsum,t2.fsum, t1.ftsum/t2.fsum as frate from ");
    sqlList.append(" (select fitypeid,sum(famount) as ftsum ");
    sqlList.append(" from t_bas_income ");
    sqlList.append(filter + " group by fitypeid) as t1, ");
    sqlList.append(" (select sum(famount) as  fsum from t_bas_income ");
    sqlList.append(filter + ") as t2 ");
    QString strSql = sqlList.join("");
    //std::cout<<strSql.toStdString()<<std::endl;
    QList<QVariantMap> ret = db.CustomSelect(strSql,QMap<QString,QVariant>());
    return ret;
}

QList<QVariantMap> bizQueryHelper::GetIncomeRptGroupByType(dbhelper db, QDate fromDate, QDate toDate, int UserId)
{
    QStringList filter;
    filter.append(ToolHelper::GetDateFilter(fromDate,toDate));
    filter.append(" FUserId="+QString::number(UserId));
    return GetIncomeRptGroupByType(db,filter.join(" And "));
}

QList<QVariantMap> bizQueryHelper::GetPaymentRptGroupByType(dbhelper db, QString filter)
{
    QStringList sqlList;
    sqlList.append(" select t1.fptypeid,t1.ftsum,t2.fsum, t1.ftsum/t2.fsum as frate from ");
    sqlList.append(" (select fptypeid,sum(famount) as ftsum ");
    sqlList.append(" from t_bas_payment ");
    sqlList.append(filter + " group by fptypeid) as t1, ");
    sqlList.append(" (select sum(famount) as  fsum from t_bas_payment ");
    sqlList.append(filter + ") as t2 ");
    QString strSql = sqlList.join("");
    //std::cout<<strSql.toStdString()<<std::endl;
    QList<QVariantMap> ret = db.CustomSelect(strSql,QMap<QString,QVariant>());
    return ret;
}

QList<QVariantMap> bizQueryHelper::GetPaymentRptGroupByType(dbhelper db, QDate fromDate, QDate toDate, int UserId)
{
    QStringList filter;
    filter.append(ToolHelper::GetDateFilter(fromDate,toDate));
    filter.append(" FUserId="+QString::number(UserId));
    return GetPaymentRptGroupByType(db,filter.join(" And "));
}

QList<QVariantMap> bizQueryHelper::GetIncomeRpt(dbhelper db, QString filter)
{
    QString strSql = "SELECT SUM(FAMOUNT) as FSUM,FYEAR,FMONTH FROM T_BAS_INCOME" + filter;
    //std::cout<<strSql.toStdString()<<std::endl;
    return db.CustomSelect(strSql,QMap<QString,QVariant>());
}

QList<QVariantMap> bizQueryHelper::GetIncomeRpt(dbhelper db, QDate fromDate, QDate toDate, int UserId)
{
    QStringList filter;
    filter.append(ToolHelper::GetDateFilter(fromDate,toDate));
    filter.append(" FUserId="+QString::number(UserId));
    return GetIncomeRpt(db,filter.join(" And ") + " group by FYEAR,FMONTH");
}

QList<QVariantMap> bizQueryHelper::GetPaymentRpt(dbhelper db, QString filter)
{
    QString strSql = "SELECT SUM(FAMOUNT) as FSUM,FYEAR,FMONTH FROM T_BAS_PAYMENT" + filter;
    std::cout<<strSql.toStdString()<<std::endl;
    return db.CustomSelect(strSql,QMap<QString,QVariant>());
}

QList<QVariantMap> bizQueryHelper::GetPaymentRpt(dbhelper db, QDate fromDate, QDate toDate, int UserId)
{
    QStringList filter;
    filter.append(ToolHelper::GetDateFilter(fromDate,toDate));
    filter.append(" FUserId="+QString::number(UserId));
    return GetPaymentRpt(db,filter.join(" And ") + " group by FYEAR,FMONTH");
}

QList<QVariantMap> bizQueryHelper::GetdayPaymentRpt(dbhelper db, QDate d, int UserId)
{
    QStringList filter;
    QString year = QString::number(d.year());
    QString month = QString::number(d.month());
    filter.append("Where FYear="+year);
    filter.append(" FMonth="+month);
    filter.append(" FUserId="+QString::number(UserId));
    QString strSql = "SELECT SUM(FAMOUNT) as FSUM,FYEAR,FMONTH,FDAY FROM T_BAS_PAYMENT" + filter.join(" And ") + " group by FDAY";
    return db.CustomSelect(strSql,QMap<QString,QVariant>());
}
